﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class VehicleMaintenanceDao
    {
        public bool Delete(SqlConnection conn, int maintenanceId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Vehicle_Maintenance] ");
            builder.Append("WHERE ");
            builder.Append("\t[MaintenanceId] = @MaintenanceId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@MaintenanceId", SqlDbType.Int) };
            commandParameters[0].Value = maintenanceId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, VehicleMaintenanceData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Vehicle_Maintenance] ");
            builder.Append("( ");
            builder.Append("\t[VehicleId], ");
            builder.Append("\t[RequestDate], ");
            builder.Append("\t[Type], ");
            builder.Append("\t[Reason], ");
            builder.Append("\t[Person], ");
            builder.Append("\t[Fee], ");
            builder.Append("\t[Remark] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@VehicleId, ");
            builder.Append("\t@RequestDate, ");
            builder.Append("\t@Type, ");
            builder.Append("\t@Reason, ");
            builder.Append("\t@Person, ");
            builder.Append("\t@Fee, ");
            builder.Append("\t@Remark ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[7];
            commandParameters[0] = new SqlParameter("@VehicleId", SqlDbType.Int);
            commandParameters[0].Value = data.VehicleId;
            commandParameters[1] = new SqlParameter("@RequestDate", SqlDbType.DateTime);
            if (data.RequestDateIsNull)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = data.RequestDate;
            }
            commandParameters[2] = new SqlParameter("@Type", SqlDbType.NVarChar, 20);
            commandParameters[2].Value = data.Type;
            commandParameters[3] = new SqlParameter("@Reason", SqlDbType.NText);
            if (data.ReasonIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.Reason;
            }
            commandParameters[4] = new SqlParameter("@Person", SqlDbType.NVarChar, 20);
            if (data.PersonIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.Person;
            }
            commandParameters[5] = new SqlParameter("@Fee", SqlDbType.Decimal);
            commandParameters[5].Precision = 0x12;
            commandParameters[5].Scale = 2;
            if (data.FeeIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.Fee;
            }
            commandParameters[6] = new SqlParameter("@Remark", SqlDbType.NText);
            if (data.RemarkIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.Remark;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectByMaintenanceId(SqlConnection conn, int maintenanceId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[MaintenanceId], ");
            builder.Append("\t[VehicleId], ");
            builder.Append("\t[RequestDate], ");
            builder.Append("\t[Type], ");
            builder.Append("\t[Reason], ");
            builder.Append("\t[Person], ");
            builder.Append("\t[Fee], ");
            builder.Append("\t[Remark] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Vehicle_Maintenance] ");
            builder.Append("WHERE ");
            builder.Append("\t[MaintenanceId] = @MaintenanceId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@MaintenanceId", SqlDbType.Int) };
            commandParameters[0].Value = maintenanceId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Vehicle_Maintenance";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, VehicleMaintenanceData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Vehicle_Maintenance]  ");
            builder.Append("SET ");
            builder.Append("\t[VehicleId] = @VehicleId, ");
            builder.Append("\t[RequestDate] = @RequestDate, ");
            builder.Append("\t[Type] = @Type, ");
            builder.Append("\t[Reason] = @Reason, ");
            builder.Append("\t[Person] = @Person, ");
            builder.Append("\t[Fee] = @Fee, ");
            builder.Append("\t[Remark] = @Remark ");
            builder.Append("WHERE ");
            builder.Append("\t[MaintenanceId] = @MaintenanceId ");
            SqlParameter[] commandParameters = new SqlParameter[8];
            commandParameters[0] = new SqlParameter("@MaintenanceId", SqlDbType.Int);
            commandParameters[0].Value = data.MaintenanceId;
            commandParameters[1] = new SqlParameter("@VehicleId", SqlDbType.Int);
            commandParameters[1].Value = data.VehicleId;
            commandParameters[2] = new SqlParameter("@RequestDate", SqlDbType.DateTime);
            if (data.RequestDateIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.RequestDate;
            }
            commandParameters[3] = new SqlParameter("@Type", SqlDbType.NVarChar, 20);
            commandParameters[3].Value = data.Type;
            commandParameters[4] = new SqlParameter("@Reason", SqlDbType.NText);
            if (data.ReasonIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.Reason;
            }
            commandParameters[5] = new SqlParameter("@Person", SqlDbType.NVarChar, 20);
            if (data.PersonIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.Person;
            }
            commandParameters[6] = new SqlParameter("@Fee", SqlDbType.Decimal);
            commandParameters[6].Precision = 0x12;
            commandParameters[6].Scale = 2;
            if (data.FeeIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.Fee;
            }
            commandParameters[7] = new SqlParameter("@Remark", SqlDbType.NText);
            if (data.RemarkIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Remark;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

